using System;
using System.Data;
using System.Data.SqlClient;
using BLL.Services.Comments.Listing;

namespace BLL.Services.Comments
{
    public class CommentsCollection
    {
        private readonly string _connectionString;

        public CommentsCollection(string connectionString)
        {
            _connectionString = connectionString;
        }

        public void Create(Comment model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Comments.Insert(conn, model.Description, model.DateOfPublish, model.UserId, model.ResponseId);
                cmd.ExecuteNonQuery();
            }
        }

        public void Delete(int commentId)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Comments.Delete(conn, commentId);
                cmd.ExecuteNonQuery();
            }
        }

        public CommentsListing GetListingComments(
            int page, int size, string column, int direction, CommentFilter filter)
        {
            if (filter == null)
                filter = new CommentFilter();

            var result = new CommentsListing
                {
                    Page = page,
                    Size = size,
                    Direction = direction,
                    Column = column,
                    Filter = filter
                };

            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Comments.GetListing(
                    conn, page, size, column, direction, filter.BookId, filter.UserId, filter.DateFrom ?? new DateTime(), filter.DateTo ?? new DateTime());

                var totalCount = new SqlParameter("@TotalCount", SqlDbType.Int, 4)
                    { Direction = ParameterDirection.ReturnValue };
                cmd.Parameters.Add(totalCount);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var comment = new CommentsListing.Comment
                            {
                                CommentId = reader.GetInt32(0),
                                DateOfPublish = reader.GetDateTime(1).ToShortDateString(),
                                BookId = reader.GetInt32(2),
                                BookTitle = reader.GetString(3),
                                UserId = reader.GetInt32(4),
                                UserEmail = reader.GetString(5)
                            };
                        result.Comments.Add(comment);
                    }
                }
                result.TotalPages = (int)totalCount.Value % size == 0
                    ? (int)totalCount.Value / size
                    : ((int)totalCount.Value / size) + 1;
            }

            return result;
        }

        public void Save(Comment model)
        {
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Comments.Update(conn, model.CommentId, model.Description, model.DateOfPublish, model.UserId, model.ResponseId);
                cmd.ExecuteNonQuery();
            }
        }

        public Comment SelectById(int commentId)
        {
            Comment result = null;
            using (var conn = new SqlConnection(_connectionString))
            {
                conn.Open();
                var cmd = StoredProcedures.Comments.SelectById(conn, commentId);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                        result = GetComment(reader);
                }
            }
            return result;
        }

        private static Comment GetComment(IDataRecord reader)
        {
            var item = new Comment();
            item.CommentId = reader.GetInt32(0);
            item.Description = reader.GetString(1);
            item.DateOfPublish = reader.GetDateTime(2);
            item.UserId = reader.GetInt32(3);
            item.ResponseId = reader.GetInt32(4);
            return item;
        }
    }
}
